-- **************************************************************************
--    Project Name:   dm层 中转费日汇总表
--    Job Name:       spmi_dm.dm_transfer_income_pay_daily_summary（分区字段：账单产生时间）
--    Description :   每次回算45天的中转费账单，按照公斤段、不按公斤段维度；发件网点到目的城市粒度；做汇总。
--    Author :        yushuo
--    date：          2022/8/05
-- **************************************************************************
--    modify by yushuo 2022/08/05 上线
-- **************************************************************************

with t1 as (
     select a.dt
          , a.send_network_code
          , a.send_network_name
          , a.receiver_province_id
          , a.receiver_province_name
          , a.destination_code
          , a.destination_name
          , a.is_sign
          , a.fee_cost
          , a.fee_type_code
          , a.package_charge_weight -- 计费重量
          , case
                when a.package_charge_weight >= 0 and a.package_charge_weight <= 0.3 then 1
                when a.package_charge_weight > 0.3 and a.package_charge_weight <= 0.5 then 2
                when a.package_charge_weight > 0.5 and a.package_charge_weight <= 1 then 3
                when a.package_charge_weight > 1 and a.package_charge_weight <= 2 then 4
                when a.package_charge_weight > 2 and a.package_charge_weight <= 3.2 then 5
                when a.package_charge_weight > 3.2 and a.package_charge_weight <= 5.2 then 6
                when a.package_charge_weight > 5.2 and a.package_charge_weight <= 10.3 then 7
                when a.package_charge_weight > 10.3 then 8
                else 1 end kg_part  -- 没重量的、脏数据的 默认为0-0.3公斤段
     from spmi_dwd.dwd_spmi_transfer_bill_new_dt a
     where dt < date_add('{{ execution_date | cst_ds }}',1)
       and dt >= date_add(date_add('{{ execution_date | cst_ds }}',1), -45)
       and is_delete = 1
     -- limit 1
 ),
      t2 as (
          select a.dt                              as bill_generation_date
               , date_add('{{ execution_date | cst_ds }}',1)                  as update_dt
               , max(b.manage_code)                As sender_manage_code
               , max(b.manage_name)                As sender_manage_name
               , max(b.provider_id)         as sender_province_id
               , max(b.provider_desc)       as sender_province_name
               , max(b.financial_center_code) as send_financial_center_code
               , max(b.financial_center_desc) as send_financial_center_name
               , max(b.city_id)                 as begin_code
               , max(b.city_desc)                 as begin_name
               , max(b.fran_code)                  as send_franchisee_code
               , max(b.fran_name)                  as send_franchisee_name
               , a.send_network_code               as send_network_code
               , a.send_network_name               as send_network_name
               , max(b.business_model_id)          as business_model_id
               , max(b.business_model_desc)        as business_model_desc
               , '全国'                              as receiver_national
               , max(a.receiver_province_id)       as receiver_province_id
               , max(a.receiver_province_name)     as receiver_province_name
               , a.destination_code                as destination_code
               , a.destination_name                as destination_name
               , cast(sum(`if`(a.fee_type_code = 'BF001'
              , 1
              , 0)) as decimal(21
              , 8))                                as sender_count
               , cast(sum(`if`(a.fee_type_code = 'BF001'
              , a.fee_cost
              , 0)) as decimal(21
              , 8))                                as accounts_sum
               , cast(sum(`if`(a.fee_type_code = 'BF001'
              , a.fee_cost
              , 0)) /
                      sum(`if`(a.fee_type_code = 'BF001'
                          , 1
                          , 0)) as decimal(21
              , 8))                                as accounts_avg
               , cast(sum(`if`(a.fee_type_code = 'BF001'
              , package_charge_weight
              , 0)) as decimal(21
              , 2))                                as sum_weight
               , cast(sum(`if`(a.fee_type_code = 'BF001'
              , package_charge_weight
              , 0)) /
                      sum(`if`(a.fee_type_code = 'BF001'
                          , 1
                          , 0)) as decimal(21
              , 2))                                as weight_avg
               , cast(sum(`if`(a.fee_type_code = 'BF003'
              , a.fee_cost
              , 0)) as decimal(21
              , 8))                                as cross_transfer_sum
               , cast(sum(`if`(a.fee_type_code = 'BF003'
              , 1
              , 0)) as decimal(21
              , 8))                                as cross_count
               , cast(sum(`if`(a.fee_type_code = 'BF003'
              , 1
              , 0)) /
                      sum(`if`(a.fee_type_code = 'BF001'
                          , 1
                          , 0)) as decimal(21
              , 8))                                as cross_avg_cnt
               , nvl(a.kg_part
              , 0)                                 as kg_part
          , a.dt as dt
          from t1 a
                   left join jms_dim.dim_network_whole_massage b
                             on a.send_network_code = b.code
          group by a.dt
                 , a.send_network_code
                 , a.send_network_name
                 , a.destination_code
                 , a.destination_name
                 , a.kg_part
          --limit 1
      )
insert overwrite table spmi_dm.dm_transfer_income_pay_daily_summary partition (dt)
 select * from t2 distribute by bill_generation_date,pmod(hash(rand()),21) ;